#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Author: guojun
# @Company: 航天神舟智慧系统技术有限公司
# @Site: https://user.qzone.qq.com/350606539/main
# @Date: 2024-08-22 16:21:20
# @Last Modified by: guojun
# @Last Modified time: 2024-08-22 16:21:20
# @Software: vscode

import os
import json
import time
from datetime import datetime
import random
import urllib.request as ur
import urllib.parse
import requests
from functools import wraps 
from PIL import Image
import dmPython 

# 时间格式化
def ftime():
    return datetime.datetime.now().strftime('%H:%M:%S')

# 重试机制
def retry(retries: int = 3, delay: float = 1):
    """
    函数执行失败时，重试

    :param retries: 最大重试的次数
    :param delay: 每次重试的间隔时间，单位 秒
    :return:
    """

    # 校验重试的参数，参数值不正确时使用默认参数
    if retries < 1 or delay <= 0:
        retries = 3
        delay = 1

    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            # 第一次正常执行不算重试次数，所以retries+1
            for i in range(retries + 1):
                try:
                    return func(*args, **kwargs)
                except Exception as e:
                    # 检查重试次数
                    if i == retries:
                        print(f"Error: {repr(e)}")
                        print(f'"{func.__doc__}" 调用失败，已重试{retries}次')
                        break
                    else:
                        print(
                            f"Error: {repr(e)}，{delay}秒后第[{i+1}/{retries}]次重试..."
                        )
                        time.sleep(delay)
        return wrapper
    return decorator

class DmDb: 
    def __init__(self,user,password,server,port):
        """
        初始化方法,创建连接对象,游标对象
        :param user: 账号(对应模式名)
        :param password:密码
        :param server: 数据库ip
        :param port: 端口
        """
        try:
            self.conn = dmPython.connect(user=user, password=password, server=server, port=port)
            self.cursor = self.conn.cursor()
            if self.cursor:
                print('>>>数据库连接成功<<<')
        except Exception as err:
            print('>>>数据库连接失败：',err)

    def init_cursor(self):
        self.cursor = self.conn.cursor()

    def __del__(self):
        # 对象被销毁的时候执行 关闭连接对象
        self.conn.close()

    def select_data(self, sql):
        """
        查询数据
        :param sql:select语句
        :return: 查询结果
        """
        try:
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Exception as err:
            print(err)

    def execute_one(self, sql, param=None):
        """
        插入一条数据
        :param sql:一条sql语句
        :return: 受影响的条数
        """
        rowcount = 0
        try:
            if param:
                self.cursor.execute(sql, param)
            else:
                self.cursor.execute(sql)
            rowcount = self.cursor.rowcount
        except Exception as err:
            #print('错误：', err, '，返回命令行参数：', rowcount) 
            self.init_cursor()
        return rowcount

    def execute_many_for(self, sql_list: list):
        """
        批量插入数据,多条insert语句装载在列表中
        :param sql_list: 多条insert语句组成的列表
        :return: 每条sql插入成功受影响的条数组成的列表, 执行失败的sql列表
        """
        failed_sql = []
        effect_rows = []
        for sql in sql_list:
            try:
                self.cursor.execute(sql)
                # 受影响的条数计数
                effect_row = self.cursor.rowcount
                effect_rows.append(effect_row)
            except Exception as err:
                print(err, f'执行异常的sql为: {sql}')
                failed_sql.append(sql)
                self.init_cursor()
        return effect_rows, failed_sql

    def del_data(self, sql):
        """
        删除数据
        :param sql:删除sql语句
        :return: 受影响的条数
        """
        try:
            self.cursor.execute(sql)
        except Exception as err:
            print(err)
        return self.cursor.rowcount

    def execute_many(self, sql, params: list):
        """
        批量执行,用于批量执行insert语句
        :param sql: sql语句, eg: 'insert into stu values(?, ?, ?, ?)'
        :param params: 对应sql语句中的参数,按位置顺序传参;参数类型为[(),()]或[[],[]]
        :return: 受影响的记录条数
        """
        try:
            self.cursor.executemany(sql, params)
            return self.cursor.rowcount
        except Exception as err:
            print(f'!!!批量插入失败: {err}, 全部回滚<<<')

    def create_user_schema(self, user_schema, password):
        """
        创建用户和模式,并对用户授权
        :param user_schema: 用户名(模式名)
        :param password: 密码
        :return:
        """
        sql = [
            rf'CREATE USER "{user_schema}" IDENTIFIED BY "{password}" HASH WITH SHA512 NO SALT PASSWORD_POLICY 2 ENCRYPT BY "{password}" LIMIT FAILED_LOGIN_ATTEMPS 3, PASSWORD_LOCK_TIME 1, PASSWORD_GRACE_TIME 10 DEFAULT TABLESPACE "MAIN";',
            rf'grant "PUBLIC","VTI","SOI" to "{user_schema}";',
            rf'grant CREATE SESSION to "{user_schema}";']
        sql2 = r'select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null'
        expected_results = (f'{user_schema}', f'{user_schema}')
        try:
            for i in sql:
                self.cursor.execute(i)
            select_results = self.select_data(sql2)
            if expected_results in select_results:
                return True
        except Exception as err:
            print(err)

    def del_user_schema(self, user_and_schema):
        """
        删除用户(模式)
        :param user_and_schema: 用户名(模式名)
        :return:
        """
        sql = f'drop user {user_and_schema} cascade;'
        sql2 = r'select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null'
        expected_results = (f'{user_and_schema}', f'{user_and_schema}')
        try:
            if expected_results in self.select_data(sql2):
                self.cursor.execute(sql)
                select_results = self.select_data(sql2)
                if expected_results not in select_results:
                    return True
            else:
                print(f'要删除的目标对象不存在: {user_and_schema}')
        except Exception as err:
            print(err) 


class PhotoUpload(object):
    def __init__(self,img_dir):
        #下载网络图片存储的路径
        self.img_dir = img_dir

        self.host = 'https://guangzhou-jczl-nansha.spacecig.com/japi/upload/' 
        self.upload_url = urllib.parse.urljoin(self.host, 'file')
        self.complete_url = urllib.parse.urljoin(self.host, 'complete')
        
        self.header1 = {
            "Accept":"text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
            "User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.100 Safari/537.36"
        }  
        self.header2 = {
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8",
            "Connection": "keep-alive",
            "Content-Length": "251",
            "Content-Type": "application/json",
            'Cookie':"CIGToken=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJjYXJkTnVtIjoiNDIwMTE2MjAyNDAzMTMxMTExIiwidXNlclN0YXRlIjoxLCJwaG9uZSI6IjEzNjU5ODkwMjExIiwiZGVwdFBhdGgiOiIvNDQvMDEvMTUvIiwidXNlck5hbWUiOiLljZfmspnnrqHnkIblkZgiLCJleHAiOjE3MjQ0MTk4MjAsInVzZXJJZCI6Ik5TUSIsImNvbnRhY3RQZXJzb25TdGF0ZSI6MH0.nJ7EHy69r9hebAqhq_8azI4kc6qmJA2WVj063CqsbH4"
	   }  

    # 发送请求
    def send_request(self,url,header,data=None,files=None):
        response = requests.post(url = url,headers = header,data = data,files = files,timeout = 20)
        if response.status_code == 200:
            try:
                return response.json()
            except Exception as e:
                print('JSON解析错误：{}'.format(e))
        else:
            print('{}-URL：{}，状态码：{}'.format(ftime(), url, response.status_code)) 
    

    # 下载网络图片到本地（设置异常重试3次，每次间隔3秒）
    @retry(retries=10, delay=3)
    def download_img(self,url,file_name): 
        '''下载网络图片到本地'''
        request = ur.Request(url = url, headers = self.header1)
        try:
            response = ur.urlopen(request)
            fullname = os.path.join(self.img_dir,file_name)
            with open(fullname, "wb") as f:
                f.write(response.read())                    # 将内容写入图片
            print(f'下载照片{file_name}成功！')
            return fullname
        except Exception as e:
            raise Exception(f'下载照片{file_name}失败,失败原因：{e}')
            return None
    
    # 检测图片是否完好
    def is_image_complete(self,image_path):
        try:
            with Image.open(image_path) as img:
                # 尝试访问图像的像素数据来确认图像是否完整
                img.load()
                # 如果没有异常抛出，说明图像被完整加载
                return True
        except Exception as e:
            # 如果有异常，图像可能不完整
            print(f"图片不完整，可能是下载未完成或源图片异常: {e}")
            return False
        
    # 调用上传接口将图片上传到文件服务器（设置异常重试3次，每次间隔3秒）
    @retry(retries=10, delay=3)
    def upload_img(self,fullname): 
        '''调用上传接口将图片上传到文件服务器'''
        files = {'file': open(fullname, 'rb')}
        response = self.send_request(url = self.upload_url,header = self.header1, files = files)   
        try: 
            file_id = response["data"]["fileId"]
            payload =json.dumps({"bucket":"sys-service-web","fileId":file_id}) 
            res = self.send_request(url = self.complete_url,header = self.header2,data = payload)    
            return file_id
        except Exception as e:
            print(f"上传照片出错,失出错原因：{e}")
            return None
                
    def start(self,db,select_sql,update_sql,update_err_sql):  
        while True:  
            rows = db.select_data(select_sql)
            if len(rows) == 0:
                break
            for row in rows:
                has_img= False
                bus_id = row[0] 
                fullname = self.download_img(row[1],row[2])
                if fullname: 
                    if self.is_image_complete(fullname):
                        file_id = self.upload_img(fullname)
                        if file_id: 
                            db.execute_one(update_sql,(file_id,bus_id))
                        has_img = True
                if not has_img:
                    db.execute_one(update_err_sql,('0',bus_id))
                    

if __name__=='__main__':     
    ######################################################
    #配置参数列表：数据库操作、文件下载路径
    #1：下载网络图片存储的路径
    data_type='设施'
    img_dir =os.path.join(r'D:\img',data_type,datetime.now().strftime('%Y-%m-%d-%H'))
    # 然后创建一个目录:
    if not os.path.exists(img_dir): 
        os.makedirs(img_dir) 

    db_user = 'CIGPROXYNS'
    #2：数据库连接参数
    db_password = 'CIGPROXYNS'
    db_host = '10.166.33.110'
    db_port = 15210
    #查询数据的SQL
    select_sql = """select id,substr(ywg_photo_url,instr(ywg_photo_url,',')+1) ywg_photo_url,
                        id||'-'||substr(ywg_photo_url,instr(ywg_photo_url,'/',-1)+1)  filename 
                    from CIGPROXYNS.ZZ_INFRASTRUCTURE
                    where length(ywg_photo_url)>0 
                        and (
                            zhaopian NOT IN(
                                SELECT FILE_ID FROM BASE_FILE_INFO
                        ) OR zhaopian IS NULL
                    )  and ywg_photo_url<>'0'
                    LIMIT 100"""
    #更新正常图片数据的SQL
    update_sql = "update CIGPROXYNS.ZZ_INFRASTRUCTURE set zhaopian=? WHERE ID=?" 
    #更新异常图片数据的SQL
    update_err_sql = "update CIGPROXYNS.ZZ_INFRASTRUCTURE set ywg_photo_url=? WHERE ID=?" 
    db = DmDb(db_user,db_password,db_host,db_port)
    ######################################################

    start = time.time()

    photoupload  = PhotoUpload(img_dir)
    photoupload.start(db,select_sql,update_sql,update_err_sql) 

    end = time.time()
    print("更新%s照片完成！！！""总耗时：%0.2fs"%(data_type,end -start))
